package org.halk.test.connect;

import org.junit.Test;

import java.sql.*;
import java.util.Properties;

/**
 * jdbc的集中连接方式
 * 数据库halk，表pers_subject
 * @Author haozhu7
 * @Date 2025/5/16 09:31
 **/
public class ConnectDemo {

    private static final String url = "jdbc:mysql://127.0.0.1:3306/halk?useSSL=true&useUnicode=true&characterEncoding=utf8";
    private static final String user = "root";
    private static final String password = "Abcd@1234";


    /**
     * 连接方式1
     */
    @Test
    public void demo1() throws SQLException {
        //获取driver对象
        Driver driver = new com.mysql.cj.jdbc.Driver();

        Properties properties = new Properties();
        properties.setProperty("user", user);
        properties.setProperty("password", password);

        //获取连接
        Connection connect = driver.connect(url, properties);

        //查询表数据
        queryAll(connect);

        //查询字段注释
        queryFieldComment(connect);
    }

    /**
     * 连接方式2
     * 对方式一的迭代，避免第三方的api，使得程序有更好的可移植性
     */
    @Test
    public void demo2() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        //获取driver对象
        Class<?> clazz = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();

        Properties properties = new Properties();
        properties.setProperty("user", user);
        properties.setProperty("password", password);

        //获取连接
        Connection connect = driver.connect(url, properties);

        //查询表数据
        queryAll(connect);

        //查询字段注释
        queryFieldComment(connect);
    }

    /**
     * 连接方式3
     * 使用DriverManger替换Driver
     */
    @Test
    public void demo3() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        //获取driver对象
        Class<?> clazz = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();

        //注册驱动
        DriverManager.registerDriver(driver);

        //获取连接
        Connection connect = DriverManager.getConnection(url, user, password);

        //查询表数据
        queryAll(connect);

        //查询字段注释
        queryFieldComment(connect);
    }

    /**
     * 连接方式4
     * 不用显示的注册驱动了，在Driver的实现类，静态代码中，注册了
     */
    @Test
    public void demo4() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
//        //获取driver对象
//        Class<?> clazz = Class.forName("com.mysql.cj.jdbc.Driver");
//        Driver driver = (Driver) clazz.newInstance();
//
//        //注册驱动
//        DriverManager.registerDriver(driver);

        //获取连接
        Connection connect = DriverManager.getConnection(url, user, password);

        //查询表数据
        queryAll(connect);

        //查询字段注释
        queryFieldComment(connect);
    }

    private static void queryFieldComment(Connection connect) throws SQLException {
        String sql = "select column_name, COLUMN_COMMENT\n" +
                "from information_schema.COLUMNS\n" +
                "where TABLE_SCHEMA = 'halk'\n" +
                "and TABLE_NAME = 'pers_subject'\n" +
                "order by ORDINAL_POSITION";
        PreparedStatement preparedStatement = connect.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String columnName = resultSet.getString("COLUMN_NAME");
            String columnComment = resultSet.getString("COLUMN_COMMENT");
            System.out.println("字段名: " + columnName + ", 注释: " + columnComment);
        }
    }

    private static void queryAll(Connection connect) throws SQLException {
        //预编译sql
        String sql = "select * from pers_subject limit 0, 3";
        PreparedStatement preparedStatement = connect.prepareStatement(sql);

        //查询
        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();

        while (resultSet.next()) {
            //打印列名
            for (int i = 0; i < metaData.getColumnCount(); i++) {
                String columnName = metaData.getColumnName(i + 1);
                System.out.print(columnName + " \t");
            }
            System.out.println();
            //打印列值
            for (int i = 0; i < metaData.getColumnCount(); i++) {
                String columnName = metaData.getColumnName(i + 1);
                String columnValue = resultSet.getString(columnName);
                System.out.print(columnValue + "\t");
            }
            System.out.println();
        }
    }
}
